create schema plpgsql_othercase;
set search_path to plpgsql_othercase;
/*
The stockpivot function takes one row from the stock table
and returns two rows to be stored in the ticker table
*/

CREATE TABLE  stocktable (
                             ticker VARCHAR2(20),
                             trade_date DATE,
                             open_price NUMBER,
                             close_price NUMBER
);

INSERT INTO stocktable select 'STK' || indx, SYSDATE, indx, indx + 15 from generate_series(1,100) as indx;

CREATE TYPE tickertype AS (
    ticker      VARCHAR2 (20)
    ,pricedate   DATE
    ,pricetype   VARCHAR2 (1)
    ,price       NUMBER
);

CREATE TYPE tickertypeset AS TABLE OF tickertype;


CREATE TABLE tickertable
(
    ticker VARCHAR2(20),
    pricedate DATE,
    pricetype VARCHAR2(1),
    price NUMBER
);

CREATE OR REPLACE PACKAGE pipeline
IS
   TYPE ticker_tt IS TABLE OF tickertype;

FUNCTION stockpivot_pl (dataset SYS_REFCURSOR)
      RETURN tickertypeset PIPELINED;
END pipeline;
/

/*
In addition to using the PIPELINED keyword in the header,
you use the PIPE ROW statement to send the value back to
the calling query, asynchronous to the function actually 
finishing and returning control.

Notice also that the RETURN statement returns nothing but
control, since all the data has already been passed back.
*/

CREATE OR REPLACE PACKAGE BODY pipeline
IS
   FUNCTION stockpivot_pl (dataset SYS_REFCURSOR)
      RETURN tickertypeset PIPELINED
   IS
      out_obj   tickertype;
      in_rec    stocktable;
   BEGIN
LOOP
FETCH dataset
    INTO in_rec;

EXIT WHEN dataset%NOTFOUND;
         -- first row
out_obj.ticker := in_rec.ticker;
out_obj.pricetype := 'O';
out_obj.price := in_rec.open_price;
out_obj.pricedate := in_rec.trade_date;
PIPE ROW (out_obj);
         -- second row
out_obj.pricetype := 'C';
out_obj.price := in_rec.close_price;
out_obj.pricedate := in_rec.trade_date;
PIPE ROW (out_obj);
END LOOP;
RETURN;
END;
END pipeline;
/

/*
Notice that a query is passed as a parameter to the function. This is not
a *string* (dynamic SQL). It is the query itself, which is then encased
within a CURSOR function call, which returns a cursor variable that is
actually passed to the body of the function for fetching.
*/
begin;
declare xc cursor with hold for SELECT * FROM stocktable;
INSERT INTO tickertable SELECT * FROM pipeline.stockpivot_pl ('xc'::refcursor);
close xc;
end;
select count(*) from  tickertable;

reset search_path;
drop schema plpgsql_othercase cascade;